package com.open.jdbc;

import com.mysql.cj.jdbc.MysqlConnectionPoolDataSource;
import com.mysql.cj.jdbc.MysqlDataSource;
import com.mysql.cj.jdbc.MysqlPooledConnection;
import com.open.jdbc.pool.ConnectionPool;

import javax.sql.DataSource;
import java.sql.*;


public class DriverManagerTest {

    public static void main(String[] args) throws SQLException {
        transactionAdd();
    }


    public static void query() throws SQLException {
        Statement statement = null;
        PreparedStatement pStatement = null;
        ResultSet resultSet = null;
        Connection connection = null;
        try {

            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");

            String sql = "SELECT * FROM testtable"; // 替换为你的表名和列名

            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);

            while (resultSet.next()) { // 遍历每一行结果
                int id = resultSet.getInt("id"); // 通过列名获取数据
                // 打印当前行数据
                System.out.printf("%-2d |", id);
            }
        } finally {
            // 6. 关闭资源，释放数据库连接 (非常重要！)[1,8](@ref)
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                System.err.println("关闭资源时发生错误: " + e.getMessage());
            }
        }
    }


    public void preventSqlInject() throws SQLException {
        Statement statement = null;
        PreparedStatement pStatement = null;
        ResultSet resultSet = null;
        Connection connection = null;
        try {

            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");

            String sql = "SELECT * FROM testtable where id = ?"; // 替换为你的表名和列名

            //防sql注入 start
            pStatement = connection.prepareStatement(sql);
            pStatement.setInt(1,0);
            resultSet = pStatement.executeQuery();
            //防sql注入 end

            while (resultSet.next()) { // 遍历每一行结果
                int id = resultSet.getInt("id"); // 通过列名获取数据
                // 打印当前行数据
                System.out.printf("%-2d |", id);
            }
        } finally {
            // 6. 关闭资源，释放数据库连接 (非常重要！)[1,8](@ref)
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                System.err.println("关闭资源时发生错误: " + e.getMessage());
            }
        }
    }


    public void bacthAdd() throws SQLException {
        Statement statement = null;
        PreparedStatement pStatement = null;
        ResultSet resultSet = null;
        Connection connection = null;
        try {

            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");

            //批量插入 start
            String batchSql1 = "insert into testtable values(?)";
            PreparedStatement pStatement2 = connection.prepareStatement(batchSql1);
            for (int i = 100; i < 110; i++) {
                pStatement2.setInt(1,i);
                //加入到批处理
                pStatement2.addBatch();
            }
            //把一批sql，一次性传给DBMS执行。
            pStatement2.executeBatch();
            //批量插入 end

        } finally {
            // 6. 关闭资源，释放数据库连接 (非常重要！)[1,8](@ref)
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                System.err.println("关闭资源时发生错误: " + e.getMessage());
            }
        }
    }



    public static void transactionAdd() throws SQLException {
        Statement statement = null;
        PreparedStatement pStatement = null;
        ResultSet resultSet = null;
        Connection connection = null;
        try {

            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
            connection.setAutoCommit(false);
            //批量插入 start
            String sql1 = "insert into testtable values(200)";
            String sql2 = "insert into testtable values(201)";
            PreparedStatement pStatement2 = connection.prepareStatement(sql1);
            pStatement2.executeUpdate();

            pStatement2 = connection.prepareStatement(sql2);
            pStatement2.executeUpdate();


            connection.commit();


        } finally {
            // 6. 关闭资源，释放数据库连接 (非常重要！)[1,8](@ref)
            try {
                if (resultSet != null) resultSet.close();
                if (statement != null) statement.close();
                if (connection != null) connection.close();
            } catch (SQLException e) {
                System.err.println("关闭资源时发生错误: " + e.getMessage());
            }
        }
    }
}
